<?php

namespace App\Console\Commands;

use App\Http\Controllers\Pc\LingXingApiController;
use App\Models\DigitalModel;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;


class LingXing extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'command:ling_xing {fun} {p1?}';


    /**
     * 临时一次性脚本
     *
     * @var string
     */
    protected $description = '领星对接';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $type = $this->argument('fun');

        switch ($type) {
            case 'addLxSku':
                //添加领星产品或者同步领星产品id到本地
                $this->addLxSku();
                break;
            case 'shipFromAddressList':
                //同步fba发货地址到本地
                $this->shipFromAddressList();
                break;
            case 'bindLxShopId':
                //绑定领星店铺id
                $this->bindLxShopId();
                break;
            case 'createSendedOrder':
                //生成领星发货单
                $this->createSendedOrder();
                break;
            case 'shipmentPlanLists':
                //同步领星的发货计划到本地
                $this->shipmentPlanLists();
                break;
            case 'mskuBindSku':
                //领星渠道sku绑定库存sku
                $this->mskuBindSku();
                break;
            case 'bindLxCategoryId':
                //绑定领星分类
                $this->bindLxCategoryId();
                break;
            case 'productExpression':
                //获取领星产品表现
                $this->productExpression();
                break;
            case 'exportSkuPrice':
                //导出产品成本
                $this->exportSkuPrice();
                break;
            case 'getOrder':
                //同步亚马逊订单
                $this->getOrder();
                break;
            case 'checkInventory':
                //盘点领星库存
                $this->checkInventory();
                break;
            case 'adCampaignReports':
                //广告活动报表
                $this->adCampaignReports();
                break;
            case 'test':
                $this->test();
                break;
                //获取库龄报告
            case 'get_storage_age':
                $this->storageAge();
                break;
                //更新订单状态
            case 'updateOrder':
                $this->updateOrder();
                break;
            case 'get_spsku_report':
                $this->spSkuReport();
                break;
            case 'get_sdsku_report':
                $this->sdSkuReport();
                break;
            default:
                exit('未知参数,程序结束');
        }

    }


    public function test()
    {
        self::writeLog(
            'test',
            ['test_1'],
            ['test_2']
        );

        echo '程序结束';
        exit;
    }

    //生成领星发货单
    public function createSendedOrder()
    {
        //领星步骤 1:创建货件计划 2:创建货件 3:生成已发货的发货单

        $amazonBuhuoRequestList = DB::table('amazon_buhuo_request')
            ->where('request_status', 7)
            ->where('is_sync_lx', 0)
            ->where('lx_seq', '')
            ->where('request_time', '>', date('Y-m-d H:i:s', strtotime('-7 days')))
            ->get();


        if ($amazonBuhuoRequestList->isEmpty()) {
            exit('没有待推到领星的单子');
        }

        $sellerList = (new LingXingApiController())->getSellerList();
        if ($sellerList['code'] != 0) {
            exit('获取领星店铺列表失败');
        }

        $sellerListKeyById = array_column($sellerList['data'], null, 'sid');

        $addressList = DB::table('lingxing_from_address')->pluck('lingxing_id', 'sid');

        $customSkuArrKeyById     = [];
        $productDetailKeyBySkuId = [];
        $skuListKeyById          = [];

        $errArr = [];//收集创建失败的补货申请单

        foreach ($amazonBuhuoRequestList as $amazonBuhuoRequest) {

            try {

                $lxApi = new LingXingApiController();
                //创建货件计划

                $requestDetailList = DB::table('amazon_buhuo_detail')->where('request_id', $amazonBuhuoRequest->id)->get();

                $shopInfo = DB::table('shop')->where('id', $amazonBuhuoRequest->shop_id)->first();
                $sid      = $shopInfo->lingxing_id;

                if (!$sid) {
                    $errArr[] = [
                        'id'      => $amazonBuhuoRequest->id,
                        'shop_id' => $amazonBuhuoRequest->shop_id,
                        'msg'     => $shopInfo->shop_name . '未绑定领星',
                    ];

                    continue;
                }

                $itemList = $createList = [];

                $addInStorage = [
                    'wid'          => $amazonBuhuoRequest->warehouse,
                    'type'         => 1,
                    'product_list' => [],
                ];

                foreach ($requestDetailList as $requestDetail) {

                    $skuInfo = DB::table('self_sku')->where('id', $requestDetail->sku_id)->first();
                    if (!$skuInfo) {
                        $errArr[] = [
                            'id'        => $amazonBuhuoRequest->id,
                            'detail_id' => $requestDetail->id,
                            'msg'       => 'sku:' . $requestDetail->sku . '不存在',
                        ];
                        continue 2;
                    }


                    $customSku = DB::table('self_custom_sku')->where('id', $skuInfo->custom_sku_id)->value('custom_sku');
                    if (!$customSku) {
                        $errArr[] = [
                            'id'        => $amazonBuhuoRequest->id,
                            'detail_id' => $requestDetail->id,
                            'msg'       => 'custom_sku:' . $requestDetail->custom_sku . '不存在',
                        ];
                        continue 2;
                    }

                    $productDetailInfo = DB::table('product_detail')->where('sku_id', $skuInfo->id)->first();
                    if (!$productDetailInfo) {
                        $errArr[] = [
                            'id'        => $amazonBuhuoRequest->id,
                            'detail_id' => $requestDetail->id,
                            'msg'       => 'sku:' . $requestDetail->sku . '详情不存在',
                        ];
                        continue 2;
                    }

                    $skuListKeyById[$skuInfo->id]                 = $skuInfo;
                    $customSkuArrKeyById[$skuInfo->custom_sku_id] = $customSku;
                    $productDetailKeyBySkuId[$skuInfo->id]        = $productDetailInfo;

                    $msku  = $productDetailInfo->sellersku;
                    $fnsku = $productDetailInfo->fnsku;

                    $itemList[] = [
                        'msku'             => $msku,
                        'fnsku'            => $fnsku,
                        'quantity_shipped' => $requestDetail->request_num,
                    ];

                    $createList[] = [
                        'seller_id'               => $sellerListKeyById[$sid]['seller_id'] ?? '',
                        'marketplace_id'          => $sellerListKeyById[$sid]['marketplace_id'] ?? '',
                        'fulfillment_network_sku' => $fnsku,
                        'sku'                     => $customSku,
                        'num'                     => $requestDetail->request_num,
                        'msku'                    => $msku,
                    ];

                    $addInStorage['product_list'][] = [
                        'sku'      => $customSku,
                        'good_num' => $requestDetail->request_num
                    ];
                }

                $fbaAddressId = $addressList[$sid] ?? 0;

                if (!$fbaAddressId) {
                    $errArr[] = [
                        'id'      => $amazonBuhuoRequest->id,
                        'shop_id' => $amazonBuhuoRequest->shop_id,
                        'msg'     => '请先添加fba发货地址',
                    ];
                    continue;
                }

                $params1 = [
                    'sid'                   => $sid,
                    'label_prep_preference' => 'SELLER_LABEL',
                    'packaging_type'        => '1',
                    'fba_address_id'        => $fbaAddressId,
                    'item_list'             => $itemList,
                ];

                $res1 = $lxApi->createInboundShipmentPlan($params1);

                LingXingApiController::writeLog('createInboundShipmentPlan', $params1, $res1);


                if ($res1['code'] != 0) {
                    $errArr[] = [
                        'id'  => $amazonBuhuoRequest->id,
                        'msg' => '创建货件计划失败',
                        'res' => $res1
                    ];
                    continue;
                }

                //生成渠道sku与货件单号键值对
                $mskuKeyByShipmentId = [];
                foreach ($res1['data']['shipments'] as $shipment) {
                    foreach ($shipment['items'] as $item) {
                        $mskuKeyByShipmentId[$item['msku']] = $shipment['shipment_id'];
                    }
                }

                //先添加库存
                $lxApi->addInStorageOrder($addInStorage);


                //创建货件
                $shipmentPlanKey = $res1['data']['shipment_plan_key'];
                $shipmentIds     = array_column($res1['data']['shipments'], 'shipment_id');

                $params2 = [
                    'shipment_plan_key' => $shipmentPlanKey,
                    'shipment_ids'      => $shipmentIds,
                ];
                $res2    = $lxApi->createInboundShipment();

                LingXingApiController::writeLog('createInboundShipmentPlan', $params2, $res2);

                if ($res2['code'] != 0) {
                    $errArr[] = [
                        'id'  => $amazonBuhuoRequest->id,
                        'msg' => '创建货件失败',
                        'res' => $res2
                    ];
                    continue;
                }

                $boxDataList = DB::table('amazon_box_data')->where('request_id', $amazonBuhuoRequest->id)->get();


                if ($boxDataList->isEmpty()) {
                    $errArr[] = [
                        'id'  => $amazonBuhuoRequest->id,
                        'msg' => '查询不到装箱数据',
                    ];

                    //删除已创建的货件
                    $lxApi->batchDeleteInboundShipment($shipmentIds, $sid);
                    continue;
                }


                $lxBoxList   = [];
                $specBoxList = [];
                //找出同箱规的箱子id
                foreach ($boxDataList as $boxData) {
                    $specBoxList[$boxData->Long . '*' . $boxData->wide . '*' . $boxData->high . '*' . $boxData->heavy][] = $boxData->box_id;
                }

                //按箱子规格整理sku数据
                foreach ($specBoxList as $key => $specBox) {
                    $boxParams = explode('*', $key);
                    $lxBox     = [
                        'box_num'           => count($specBox),
                        'cg_box_length'     => $boxParams[0],
                        'cg_package_width'  => $boxParams[1],
                        'cg_package_height' => $boxParams[2],
                        'cg_box_weight'     => $boxParams[3],
                        'box_skus'          => [],
                    ];

                    foreach ($specBox as $boxId) {
                        foreach ($requestDetailList as $buhuoDetail) {
                            $boxDataDetail = json_decode($buhuoDetail->box_data_detail, true);
                            if (isset($lxBox['box_skus'][$buhuoDetail->custom_sku_id])) {
                                $lxBox['box_skus'][$buhuoDetail->custom_sku_id]['num'] += $boxDataDetail[$boxId - 1];
                            } else {
                                $lxBox['box_skus'][$buhuoDetail->custom_sku_id]['num']           = $boxDataDetail[$boxId - 1];
                                $lxBox['box_skus'][$buhuoDetail->custom_sku_id]['custom_sku_id'] = $buhuoDetail->custom_sku_id;
                                $lxBox['box_skus'][$buhuoDetail->custom_sku_id]['sku_id']        = $buhuoDetail->sku_id;
                            }
                        }

                    }
                    $lxBoxList[] = $lxBox;
                }

                //把数据整理成领星需要的格式
                $newLxBoxList = [];
                foreach ($lxBoxList as $lxBox) {
                    foreach ($lxBox['box_skus'] as $skuDetail) {
                        if ($skuDetail['num'] == 0) continue;

                        $skuInfo    = $skuListKeyById[$skuDetail['sku_id']];
                        $shipmentId = $mskuKeyByShipmentId[$skuInfo->old_sku] ?? $mskuKeyByShipmentId[$skuInfo->sku] ?? '';

                        $lxBox['box_skus_new'][] = [
                            'seller_id'               => $sellerListKeyById[$sid]['seller_id'] ?? '',
                            'marketplace_id'          => $sellerListKeyById[$sid]['marketplace_id'] ?? '',
                            'shipment_id'             => $shipmentId,
                            'fulfillment_network_sku' => $productDetailKeyBySkuId[$skuDetail['sku_id']]->fnsku,
                            'quantity_in_case'        => $skuDetail['num'],
                            'sku'                     => $customSkuArrKeyById[$skuDetail['custom_sku_id']],
                        ];
                    }
                    $lxBox['box_skus'] = $lxBox['box_skus_new'];
                    unset($lxBox['box_skus_new']);
                    $newLxBoxList[] = $lxBox;
                }


                foreach ($createList as $k => $v) {
                    $createList[$k]['shipment_id'] = $mskuKeyByShipmentId[$v['msku']];
                    unset($createList[$k]['msku']);
                }


                //生成已发货的发货单
                $params3 = [
                    'wid'      => $amazonBuhuoRequest->warehouse,
                    'list'     => $createList,
                    'box_type' => 'MULTIPLE', // 装箱类型： SINGLE 每箱只允许一款SKU MULTIPLE 每箱允许多款SKU
                    'box_list' => $newLxBoxList,
                ];

                $res3 = $lxApi->createSendedOrder();

                LingXingApiController::writeLog('createInboundShipmentPlan', $params3, $res3);

                if ($res3['code'] != 0) {
                    $errArr[] = [
                        'id'  => $amazonBuhuoRequest->id,
                        'msg' => '生成已发货的发货单失败',
                        'res' => $res3
                    ];

                    //删除已创建的货件
                    $lxApi->batchDeleteInboundShipment($shipmentIds, $sid);
                    continue;
                }

                //修改是否同步到领星的状态
                DB::table('amazon_buhuo_request')->where('id', $amazonBuhuoRequest->id)->update(['is_sync_lx' => 1]);


            } catch (\Exception $exception) {
                $errArr[] = [
                    'id'   => $amazonBuhuoRequest->id,
                    'line' => $exception->getLine(),
                    'msg'  => $exception->getMessage(),
                ];

                if (isset($shipmentIds) && isset($sid)) {
                    //删除已创建的货件
                    $lxApi->batchDeleteInboundShipment($shipmentIds, $sid);
                }


            }

//            exit('退出第一次循环');
        }


        //记录错误日志
        if ($errArr) {
            self::writeLog('lxCreateSendedOrder', [], $errArr);
        }

        exit('程序结束');
    }

    //同步领星补货计划到本地
    public function shipmentPlanLists()
    {
//        $params = [
//            'start_date' => '2023-10-08',
//            'end_date'   => '2023-10-18',
//            'length'     => 100,
//        ];
//
//        $shipmentPlanLists = (new LingXingApiController())->shipmentPlanLists($params);

        $shipmentPlanLists = $this->getShipmentPlanLists();
        if ($shipmentPlanLists['code'] != 0) {
            self::writeLog('shipmentPlanLists', null, $shipmentPlanLists);
        }

        $digitalModel = $model = new DigitalModel();

        $warehouseIdKeyByLx = [
            '6293' => 1,
            '6294' => 2,
        ];


        foreach ($shipmentPlanLists['data'] as $shipmentPlan) {
            $userName = $shipmentPlan['create_user'];
            $userInfo = DB::table('users')->where('account', $userName)->first();

            $wid = $shipmentPlan['list'][0]['wid'];//领星仓库id

            $warehouseId = $warehouseIdKeyByLx[$wid] ?? 1;

            $seq = $shipmentPlan['seq'];//计划编号

            $params = [
                'warehouse'         => $warehouseId,
                'user_fz'           => '',
                'user_sh'           => '',
                'user_cj'           => '',
                'start_time'        => date('Y-m-d'),
                'end_time'          => date('Y-m-d'),
                'user_name'         => $userName,
                'user_id'           => $userInfo->Id,
                'transportation_id' => 1, // todo 运输方式
                'token'             => '',
            ];

            $data = [];

            foreach ($shipmentPlan['list'] as $v) {
                $shopId = DB::table('shop')->where('lingxing_id', $v['sid'])->value('id');

                $customSkuInfo = DB::table('self_custom_sku')->where('custom_sku', $v['sku'])->first();

                $skuInfo = DB::table('self_sku')->where('sku', $v['msku'])->orWhere('old_sku', $v['msku'])->first();

                $data[] = [
                    'quanzhou_auto_num' => $warehouseId == 2 ? $v['shipment_plan_quantity'] : 0,
                    'tongan_auto_num'   => $warehouseId == 1 ? $v['shipment_plan_quantity'] : 0,
                    'cloud_auto_num'    => 0,
                    'shop_id'           => $shopId,
                    'custom_sku'        => $v['sku'],
                    'sputype'           => 1,
                    'request_num'       => $v['shipment_plan_quantity'],
                    'custom_sku_id'     => $customSkuInfo->id,
                    'sku'               => $v['msku'],
                    'spu_id'            => $customSkuInfo->spu_id,
                    'sku_id'            => $skuInfo->id,
                    'user_id'           => $skuInfo->user_id,
                    'spu'               => $customSkuInfo->spu,
                ];
            }
//            dd($data);

            $params['data'] = $data;

            $res = $digitalModel->create_buhuoDoc_auto($params);

            dd($res);


        }
    }

    public function getShipmentPlanLists()
    {
        $str = <<<aa
{"code":0,"message":"success","error_details":[],"request_id":"EC1E2619-8791-8F1C-B974-9E9A51AC954A","response_time":"2023-10-19 09:09:00","data":[{"ispg_id":1108,"create_time":"2023-10-09 15:12:27","seq":"RP231009002","remark":"","create_user":"\u738b\u82e5\u99a8","list":[{"ispg_id":1108,"isp_id":4374,"logistics_channel_id":1560,"fnsku":"X003KESF5V","msku":"GL035-CUBPO01923-ORN-M-PARTNEW","wid":3635,"wname":"\u53a6\u95e8\u540c\u5b89FBA\u4ed3","sid":2624,"create_time":"2023-10-09 15:12:27","status":0,"packing_type":1,"shipment_time":"","shipment_plan_quantity":2,"seq":"RP231009002","logistics_name":"\u6d77\u8fd0SCK4","quantity_in_case":1,"box_num":2,"is_relate_mws":0,"is_relate_list":0,"remark":"","print_num":0,"create_user":"\u738b\u82e5\u99a8","small_image_url":"https:\/\/m.media-amazon.com\/images\/I\/81kSi6if8XL._SL75_.jpg","order_sn":"R231009004","product_name":"\u7f8e\u56fd\u7537\u7ae5Polo01923\u6a59\u8272M","product_id":193891,"sku":"CUBPO01923-ORN-M","product_valid_num":0,"product_qc_num":null,"pic_url":"https:\/\/image.distributetop.com\/lingxing-erp\/901318718749066240\/20230910\/150ae24ae0e84280902ff64c91bc05a2.jpg","is_combo":0,"storage_list":[{"product_id":193891,"product_valid_num":0,"product_qc_num":0,"quantity_receive":0}],"son_storage_arr":[],"mws_relate":[],"list_relate":[],"status_name":"\u5f85\u5ba1\u6279","packing_type_name":"\u6df7\u88c5","diff_num":-2,"sname":"Geeklight-us","nation":"\u7f8e\u56fd"},{"ispg_id":1108,"isp_id":4373,"logistics_channel_id":1560,"fnsku":"X003KCQVF9","msku":"GL035-CUMSS01737-GRE-3XL","wid":3635,"wname":"\u53a6\u95e8\u540c\u5b89FBA\u4ed3","sid":2624,"create_time":"2023-10-09 15:12:27","status":0,"packing_type":1,"shipment_time":"","shipment_plan_quantity":1,"seq":"RP231009002","logistics_name":"\u6d77\u8fd0SCK4","quantity_in_case":1,"box_num":1,"is_relate_mws":0,"is_relate_list":0,"remark":"","print_num":0,"create_user":"\u738b\u82e5\u99a8","small_image_url":"https:\/\/m.media-amazon.com\/images\/I\/81VYbm+CUUL._SL75_.jpg","order_sn":"R231009003","product_name":"\u7f8e\u56fd\u7537\u88c5\u77ed\u8896\u4ea8\u522901737\u7070\u82723XL","product_id":193355,"sku":"CUMSS01737-GRE-3XL","product_valid_num":0,"product_qc_num":null,"pic_url":"https:\/\/image.distributetop.com\/lingxing-erp\/901318718749066240\/20230825\/5c844cece985485c9c8fd11cafac3263.jpg","is_combo":0,"storage_list":[{"product_id":193355,"product_valid_num":0,"product_qc_num":0,"quantity_receive":0}],"son_storage_arr":[],"mws_relate":[],"list_relate":[],"status_name":"\u5f85\u5ba1\u6279","packing_type_name":"\u6df7\u88c5","diff_num":-1,"sname":"Geeklight-us","nation":"\u7f8e\u56fd"},{"ispg_id":1108,"isp_id":4372,"logistics_channel_id":1560,"fnsku":"X003KCQVF9","msku":"GL035-CUMSS01737-GRE-3XL","wid":3635,"wname":"\u53a6\u95e8\u540c\u5b89FBA\u4ed3","sid":2624,"create_time":"2023-10-09 15:12:27","status":0,"packing_type":1,"shipment_time":"","shipment_plan_quantity":1,"seq":"RP231009002","logistics_name":"\u6d77\u8fd0SCK4","quantity_in_case":1,"box_num":1,"is_relate_mws":0,"is_relate_list":0,"remark":"","print_num":0,"create_user":"\u738b\u82e5\u99a8","small_image_url":"https:\/\/m.media-amazon.com\/images\/I\/81VYbm+CUUL._SL75_.jpg","order_sn":"R231009002","product_name":"\u7f8e\u56fd\u7537\u88c5\u77ed\u8896\u4ea8\u522901737\u7070\u82723XL","product_id":193355,"sku":"CUMSS01737-GRE-3XL","product_valid_num":0,"product_qc_num":null,"pic_url":"https:\/\/image.distributetop.com\/lingxing-erp\/901318718749066240\/20230825\/5c844cece985485c9c8fd11cafac3263.jpg","is_combo":0,"storage_list":[{"product_id":193355,"product_valid_num":0,"product_qc_num":0,"quantity_receive":0}],"son_storage_arr":[],"mws_relate":[],"list_relate":[],"status_name":"\u5f85\u5ba1\u6279","packing_type_name":"\u6df7\u88c5","diff_num":-1,"sname":"Geeklight-us","nation":"\u7f8e\u56fd"}]},{"ispg_id":1107,"create_time":"2023-10-09 15:09:22","seq":"RP231009001","remark":"","create_user":"\u738b\u82e5\u99a8","list":[{"ispg_id":1107,"isp_id":4371,"logistics_channel_id":1560,"fnsku":"X003KESF5V","msku":"GL035-CUBPO01923-ORN-M-PARTNEW","wid":3635,"wname":"\u53a6\u95e8\u540c\u5b89FBA\u4ed3","sid":2624,"create_time":"2023-10-09 15:09:22","status":0,"packing_type":1,"shipment_time":"","shipment_plan_quantity":2,"seq":"RP231009001","logistics_name":"\u6d77\u8fd0SCK4","quantity_in_case":1,"box_num":2,"is_relate_mws":0,"is_relate_list":0,"remark":"","print_num":0,"create_user":"\u738b\u82e5\u99a8","small_image_url":"https:\/\/m.media-amazon.com\/images\/I\/81kSi6if8XL._SL75_.jpg","order_sn":"R231009001","product_name":"\u7f8e\u56fd\u7537\u7ae5Polo01923\u6a59\u8272M","product_id":193891,"sku":"CUBPO01923-ORN-M","product_valid_num":0,"product_qc_num":null,"pic_url":"https:\/\/image.distributetop.com\/lingxing-erp\/901318718749066240\/20230910\/150ae24ae0e84280902ff64c91bc05a2.jpg","is_combo":0,"storage_list":[{"product_id":193891,"product_valid_num":0,"product_qc_num":0,"quantity_receive":0}],"son_storage_arr":[],"mws_relate":[],"list_relate":[],"status_name":"\u5f85\u5ba1\u6279","packing_type_name":"\u6df7\u88c5","diff_num":-2,"sname":"Geeklight-us","nation":"\u7f8e\u56fd"}]}],"total":2}
aa;

        return json_decode($str, true);
    }


//    //绑定领星店铺id
//    public function bindLxShopIdBac()
//    {
//        $lxApi = new LingXingApiController();
//
//        $res = $lxApi->getSellerList();
//
//
//        $sellerKeyById = array_column($res['data'], 'sid', 'name');
//        $shopList      = DB::table('shop')
//            ->select('id', 'shop_name')
//            ->where('platform_id', 5)
//            ->get();
//
//        foreach ($shopList as $shop) {
//            $lingXingId = $sellerKeyById[trim($shop->shop_name)] ?? 0;
//            if (!$lingXingId) continue;
//
//            DB::table('shop')->where('id', $shop->id)->update(['lingxing_id' => $lingXingId]);
//        }
//
//
//        exit('程序结束');
//    }

    //绑定领星店铺id
    public function bindLxShopId()
    {
        $lxApi = new LingXingApiController();
        $res   = $lxApi->getSellerList();

        if ($res['code'] != 0) {
            exit(json_encode($res, JSON_UNESCAPED_UNICODE));
        }


        $shopList = DB::table('shop')->where('platform_id', 5)->get();

        foreach ($res['data'] as $seller) {
            foreach ($shopList as $shop) {
                if ($seller['name'] != $shop->shop_name) continue;
                if ($shop->lingxing_id) continue;

                $appData = json_decode($shop->app_data, true);


                if ($appData) {
                    $appData['MARKETPLACE_ID'] = $seller['marketplace_id'];
                    $appData['MERCHANT_ID']    = $seller['seller_id'];
                } else {
                    $appData = [
                        'MARKETPLACE_ID' => $seller['marketplace_id'],
                        'MERCHANT_ID'    => $seller['marketplace_id'],
                    ];
                }

                DB::table('shop')->where('id', $shop->Id)->update([
                    'lingxing_id' => $seller['sid'],
                    'app_data'    => json_encode($appData, JSON_UNESCAPED_UNICODE),
                ]);
            }

        }

        exit('程序结束');
    }

    //初始化领星库存
    public function initialInventory()
    {

        $locationNumList = DB::table('cloudhouse_location_num as a')
            ->leftjoin('cloudhouse_warehouse_location as b', 'a.location_id', '=', 'b.id')
            ->select([
                'a.custom_sku_id',
                'b.warehouse_id',
                DB::raw("sum(a.num) as num"),
            ])
            ->where('a.platform_id', 5)
            ->where('a.num', '>', 0)
            ->where('a.custom_sku', 'ZITY1222-GRE-S')
            ->groupBy('a.custom_sku_id', 'b.warehouse_id')
            ->get();

        $count = $locationNumList->count();

        $customSkuIds  = $locationNumList->pluck('custom_sku_id');
        $customSkuList = DB::table('self_custom_sku')->whereIn('id', $customSkuIds)->pluck('custom_sku', 'id');


        $num = 1;
        foreach ($locationNumList as $locationNum) {
            echo $count . '___' . $num . PHP_EOL;
            $num++;

            $customSku = $customSkuList[$locationNum->custom_sku_id] ?? '';

            if (!$customSku) {
                continue;
            }

            $lxApi = new LingXingApiController();
            $res   = $lxApi->addInStorageOrder([
                'wid'          => $locationNum->warehouse_id, // 仓库id,喜马拉亚自己的id
                'type'         => 1, // 1:手工入库 2:采购入库 26:退货入库 27:移除入库
                'product_list' => [
                    [
                        'sku'      => $customSku, //库存sku，全部用新的
                        'good_num' => $locationNum->num,//良品数量
                    ],
                ],
            ]);

        }

        exit('程序结束');
    }

    //盘点领星库存
    public function checkInventory()
    {
        $warehouseList = DB::table('cloudhouse_warehouse')->select('id', 'warehouse_name', 'lingxing_id')->whereIn('id', [1, 2])->get();


        foreach ($warehouseList as $warehouse) {
            echo $warehouse->id . PHP_EOL;

            $lxApi = new LingXingApiController();

            //领星库存
            $p                 = 1;//页数
            $length            = 400;
            $inventoryKeyBySku = [];
            do {
                $page             = ($p - 1) * $length;
                $inventoryDetails = $lxApi->inventoryDetails($warehouse->lingxing_id, $page, $length);

                foreach ($inventoryDetails['data'] as $inventoryInfo) {
                    $inventoryKeyBySku[$inventoryInfo['sku']] = $inventoryInfo['product_total'];
                }

                $totalPage = ceil($inventoryDetails['total'] / $length);
                $p++;


            } while ($p <= $totalPage);

            dump($inventoryKeyBySku);


            //本地库存
            $locationNumList = DB::table('cloudhouse_location_num as a')
                ->leftjoin('self_custom_sku as b', 'a.custom_sku_id', '=', 'b.id')
                ->leftjoin('cloudhouse_warehouse_location as c', 'a.location_id', '=', 'c.id')
                ->select([
                    'b.custom_sku',
                    DB::raw("sum(a.num) as num"),
                ])
                ->where('a.platform_id', 5)
                ->where('c.warehouse_id', 1)
                ->groupBy('a.custom_sku_id')
                ->get()
                ->pluck('num', 'custom_sku')
                ->toArray();

            $count = count($locationNumList);
            $n     = 1;

            foreach ($locationNumList as $customSku => $num) {
                echo $warehouse->id . '___' . $count . '___' . $n . PHP_EOL;
                $n++;

                if (isset($inventoryKeyBySku[$customSku]) && $inventoryKeyBySku[$customSku] == $num) continue;

                $balance = $num - ($inventoryKeyBySku[$customSku] ?? 0);

                if ($balance == 0) continue;

                if ($balance > 0) {
                    //添加入库单
                    $lxApi->addInStorageOrder([
                        'wid'          => $warehouse->id,
                        'type'         => 1,
                        'product_list' => [
                            [
                                'sku'      => $customSku,
                                'good_num' => $balance,
                            ]
                        ],
                    ]);


                } else {
                    //添加出库单
                    $lxApi->addOutStorageOrder([
                        'wid'          => $warehouse->id,
                        'type'         => 11,
                        'product_list' => [
                            [
                                'sku'      => $customSku,
                                'good_num' => -$balance,
                            ]
                        ],
                    ]);
                }
            }
        }

        exit('程序结束');
    }


    //亚马逊sku绑定产品(匹配未匹配的)
    public function mskuBindSkuBak()
    {
        $sellerList = (new LingXingApiController())->getSellerList();

        $customSkuList = DB::table('self_custom_sku')->pluck('custom_sku', 'id');

        $oldSku = DB::table('self_sku')->pluck('custom_sku_id', 'old_sku');
        $newSku = DB::table('self_sku')->pluck('custom_sku_id', 'sku');

        $bindArr = [];

        print_r($sellerList);

        foreach ($sellerList['data'] as $seller) {
            $listingList = (new LingXingApiController())->getListing(0, 100000, $seller['sid'], 2);

            foreach ($listingList['data'] as $listing) {
                if ($listing['status'] == 0 || $listing['is_delete'] == 1) continue;


                $mSku        = $listing['seller_sku'];
                $customSkuId = $oldSku[$mSku] ?? 0;
                $customSkuId = $customSkuId ? $customSkuId : ($newSku[$mSku] ?? 0);

                if (!$customSkuId) continue;

                $customSku = $customSkuList[$customSkuId] ?? '';

                if (!$customSku) continue;

                $bindArr[] = [
                    'sku'         => $customSku,
                    'msku'        => $mSku,
                    'is_sync_pic' => '1',
                ];
            }
        }

        $errArr = [];

        $count = count($bindArr);

        foreach ($bindArr as $key => $value) {

            echo $count . '___' . $key . PHP_EOL;

            try {
                $lxApi = new LingXingApiController();
                $res   = $lxApi->bindMsku(['data' => [$value]]);
                if ($res['code'] != 0) {

                    //领星未录入产品时，重新录下产品
                    if ($res['code'] != 0 && isset($res['error_details'][0]['message']) &&
                        strstr($res['error_details'][0]['message'], '本地sku')) {
                        $res = $lxApi->addSkuAuto($value['sku']);
                        if ($res['code'] != 0) {
                            $errArr[] = $value;
                            echo json_encode($res, JSON_UNESCAPED_UNICODE) . PHP_EOL;
                            continue;
                        }

                    }

                    $value['is_sync_pic'] = 0;
                    $res                  = $lxApi->bindMsku(['data' => [$value]]);
                    if ($res['code'] != 0) {
                        echo json_encode($res, JSON_UNESCAPED_UNICODE) . PHP_EOL;
                        $errArr[] = $value;
                    }
                }

            } catch (\Exception $exception) {
                echo $exception->getLine() . PHP_EOL;
                echo $exception->getMessage() . PHP_EOL;

                $errArr[] = $value;
            }

        }

        echo json_encode($errArr, JSON_UNESCAPED_UNICODE);

        exit('程序结束');
    }

    public function mskuBindSku()
    {
        $sellerList = (new LingXingApiController())->getSellerList();

        $sids = implode(',', array_column($sellerList['data'], 'sid'));

        $bindArr   = [];
        $p         = 1;    //页码
        $length    = 100;  //页数
        $totalPage = 0;    //总页数

        do {
            $listingList = (new LingXingApiController())->getListing(($p - 1) * $length, $length, $sids, 2);

            foreach ($listingList['data'] as $listing) {
                if ($listing['status'] == 0 || $listing['is_delete'] == 1){
                    echo $listing['seller_sku'].'--'.$listing['status'];
                    continue;
                }

                $mSku = $listing['seller_sku'];

                $customSkuId = DB::table('self_sku')->where('sku', $mSku)->orWhere('old_sku', $mSku)->value('custom_sku_id');

                if (!$customSkuId) continue;

                $customSku = DB::table('self_custom_sku')->where('id', $customSkuId)->value('custom_sku');

                if (!$customSku) continue;

                $bindArr[] = [
                    'sku'         => $customSku,
                    'msku'        => $mSku,
                    'is_sync_pic' => '1',
                ];
            }

            !$totalPage && $totalPage = ceil($listingList['total'] / $length);
            $p++;
        } while ($p <= $totalPage);


        $errArr = [];

        $count = count($bindArr);

        foreach ($bindArr as $key => $value) {

            echo $count . '___' . $key . PHP_EOL;

            try {
                $lxApi = new LingXingApiController();
                $res   = $lxApi->bindMsku(['data' => [$value]]);
                if ($res['code'] != 0) {

//                    //领星未录入产品时，重新录下产品
//                    if ($res['code'] != 0 && isset($res['error_details'][0]['message']) &&
//                        strstr($res['error_details'][0]['message'], '本地sku')) {
//                        $res = $lxApi->addSkuAuto($value['sku']);
//                        if ($res['code'] != 0) {
//                            $errArr[] = $value;
//                            echo json_encode($res, JSON_UNESCAPED_UNICODE) . PHP_EOL;
//                            continue;
//                        }
//
//                    }
//
//                    $value['is_sync_pic'] = 0;
//                    $res                  = $lxApi->bindMsku(['data' => [$value]]);
                    if ($res['code'] != 0) {
                        echo json_encode($res, JSON_UNESCAPED_UNICODE) . PHP_EOL;
                        $errArr[] = $value;
                    }
                }

            } catch (\Exception $exception) {
                echo $exception->getLine() . PHP_EOL;
                echo $exception->getMessage() . PHP_EOL;

                $errArr[] = $value;
            }

        }

        echo json_encode($errArr, JSON_UNESCAPED_UNICODE);

        exit('程序结束');
    }


    //添加翎星SKU
    public function addLxSku()
    {
        $skuList = DB::table('self_custom_sku as a')
            ->leftJoin('self_sku as b', 'a.id', '=', 'b.custom_sku_id', '=', 'a.id')
            ->leftJoin('shop as c', 'b.shop_id', '=', 'c.Id')
            ->where('a.lingxing_id', 0)
            ->where('c.platform_id', 5)
            ->select('a.id', 'a.custom_sku', 'a.name', 'a.img', 'a.buy_price', 'a.spu_id', 'a.base_spu_id')
            ->get();

        print_r($skuList);
        $spuList = DB::table('self_spu')->get()->keyBy('id');

        $priceKeyBySpuId = $this->getContractPrice();//获取合同价格

        $spuInfoByBaseSpuId = Db::table('self_spu_info')->pluck('unit_price', 'base_spu_id');

        $categoryList = Db::table('self_category')->where('type', 3)->pluck('lingxing_id', 'id');


        $count = $skuList->count();
        $n = 1;

        foreach ($skuList as $sku) {
            echo $count . '___' . $n . PHP_EOL;
            $n++;

            try {

                $lxApi = new LingXingApiController();

//                $skuInfo = $lxApi->getSkuInfo($sku->custom_sku);
//
//                if ($skuInfo['code'] == 0) {
//                    DB::table('self_custom_sku')->where('id', $sku->id)->update(['lingxing_id' => $skuInfo['data']['id']]);
//                    continue;
//                }


                $name = $sku->name;

                if (strstr($name, ' ')) {
                    $name = str_replace(' ', '', $name);
                } elseif (strstr($name, "\n")) {
                    $name = str_replace("\n", '', $name);
                } elseif (!$name) {
                    $name = $sku->custom_sku;
                }

                $categoryId = 0;

                if ($sku->spu_id) {
                    $threeCateId = $spuList[$sku->spu_id]->three_cate_id ?? null;
                    $categoryId = $threeCateId ? ($categoryList[$threeCateId] ?? 0) : 0;
                }

                $price = $priceKeyBySpuId[$sku->spu_id] ?? 0;

                if (!$price) {
                    $price = $spuInfoByBaseSpuId[$sku->base_spu_id] ?? 0;
                }

                $addSkuArr = [
                    'sku' => $sku->custom_sku,
                    'product_name' => $name,
//                    'picture_list' => ['pic_url' => $sku->img, 'is_primary' => 1],
                    'cg_price' => round($price * 1.1, 2),
                    'category_id' => $categoryId
                ];
                if ($sku->img) {
                    $addSkuArr['picture_list'][0] = ['pic_url' => $sku->img, 'is_primary' => 1];
                }
                $res = $lxApi->addSku($addSkuArr);
                print_r($res);
                if ($res['code'] == 0) {
                    DB::table('self_custom_sku')->where('id', $sku->id)->update(['lingxing_id' => $res['data']['product_id']]);
                }
//                else {
//                    dump($res);
//                }

            } catch (\Exception $exception) {
                echo $exception->getLine() . PHP_EOL;
                echo $exception->getMessage() . PHP_EOL;
            }
        }


        exit('程序结束');
    }

    //绑定翎星分类id
    public function bindLxCategoryId()
    {
        //整理翎星分类id
        $lxApi = new LingXingApiController();

        $lxCateList = $lxApi->getCategory(0, 1000)['data'];

        $lxCateByTitle = []; // 示例: "POLO_男童_短袖POLO" => 1575
        $threeOneCate  = []; //三级分类对应的一级分类
        foreach ($lxCateList as $key1 => $value1) {
            if ($value1['parent_cid']) continue;

            foreach ($lxCateList as $value2) {
                if ($value2['parent_cid'] != $value1['cid']) continue;

                foreach ($lxCateList as $value3) {
                    if ($value3['parent_cid'] != $value2['cid']) continue;

                    $lxCateByTitle[$value1['title'] . '_' . $value2['title'] . '_' . $value3['title']] = $value3['cid'];

                    $threeOneCate[$value3['title']] = $value1['title'];
                }
            }
        }

        $categoryList = Db::table('self_category')->where('status', 1)->get()->keyBy('Id');

        foreach ($categoryList as $value) {
            if ($value->type != 3 || $value->lingxing_id > 0) continue;


            $twoCate = $categoryList[$value->fid];
            $oneCate = $categoryList[$twoCate->fid];

            $oneCateName = $oneCate->name;
            $threeClass  = $value->name;

            if ($oneCateName == '工艺品') {
                $oneClass = $oneCateName;
                $twoClass = $twoCate->name;
            } else {
                $oneClass = $threeOneCate[$threeClass] ?? '';
                $twoClass = $oneCate->name;
            }

            if ($threeClass == '雨衣') {
                $oneClass   = $threeClass;
                $threeClass .= '*';
            }

            $key = $oneClass . '_' . $twoClass . '_' . $threeClass;

            if (!isset($lxCateByTitle[$key])) continue;


            Db::table('self_category')->where('id', $value->Id)->update(['lingxing_id' => $lxCateByTitle[$key]]);
        }

        exit('程序结束');
    }

    //fba发货地址
    public function shipFromAddressList()
    {

        $addressList = (new LingXingApiController())->shipFromAddressList();


        foreach ($addressList['data'] as $address) {

            $shopId = DB::table('shop')->where('lingxing_id', $address['sid'])->value('id');

            DB::table('lingxing_from_address')->insert([
                'lingxing_id'         => $address['id'],
                'sid'                 => $address['sid'],
                'alias_name'          => $address['alias_name'],
                'country_code'        => $address['country_code'],
                'sender_name'         => $address['sender_name'],
                'province'            => $address['province'],
                'city'                => $address['city'],
                'region'              => $address['region'],
                'street_detail1'      => $address['street_detail1'],
                'street_detail2'      => $address['street_detail2'],
                'zip_code'            => $address['zip_code'],
                'phone'               => $address['phone'],
                'is_default'          => $address['is_default'],
                'seller_name'         => $address['seller_name'],
                'seller_country_name' => $address['seller_country_name'],
                'country_name'        => $address['country_name'],
                'shop_id'             => $shopId ?: 0,
            ]);

        }

        exit('程序结束');
    }


    //产品表现
    public function productExpression()
    {
        //获取领星所有店铺
        $sellerList = (new LingXingApiController())->getSellerList();
        if ($sellerList['code'] != 0) {
            exit(json_encode($sellerList, JSON_UNESCAPED_UNICODE));
        }

        $type = $this->argument('p1');//1:更新最近2天  2:更新前天的数据

        $dateArr = [];

        if ($type == 1) {
            for ($i = 1; $i <= 3; $i++) {
                $dateArr[] = date('Y-m-d', strtotime("-$i days"));
            }
        } elseif ($type == 2) {
            $dateArr[] = date('Y-m-d', strtotime("-2 days"));
        }


        $shopIdKeyByLx = DB::table('shop')->where('lingxing_id', '>', 0)->pluck('id', 'lingxing_id');

        foreach ($dateArr as $date) {
            $this->productExpressionFun($date, $sellerList, $shopIdKeyByLx);
        }


        exit('程序结束');
    }


    public function productExpressionFun($date, $sellerList, $shopIdKeyByLx)
    {
        $count = count($sellerList['data']);
        $n     = 1;

        foreach ($sellerList['data'] as $seller) {
            echo $date . '___' . $count . '___' . $n . PHP_EOL;
            $n++;


            $p         = 1;    //页码
            $length    = 100;  //页数
            $totalPage = 0;    //总页数

            do {
                echo $date . '___' . $count . '___' . ($n - 1) . '___' . $totalPage . '___' . $p . PHP_EOL;


                $params = [
                    'offset'        => ($p - 1) * $length,
                    'length'        => $length,
                    'summary_field' => 'asin',
                    'sid'           => $seller['sid'],
                    'start_date'    => $date,
                    'end_date'      => $date,
                ];

                try {
                    sleep(1);
                    $res = (new LingXingApiController())->asinList($params);
                    if ($res['code'] == 0) {
                        foreach ($res['data']['list'] as $value) {
                            $cateRank        = $value['cate_rank'] ?: 0;
                            $avgStar         = $value['avg_star'] ?: 0;
                            $reviewsCount    = $value['reviews_count'] ?: 0;
                            $sessions        = $value['sessions'] ?: 0;
                            $sessionsMobile  = $value['sessions_mobile'] ?: 0;
                            $sessionsTotal   = $value['sessions_total'] ?: 0;
                            $pageViews       = $value['page_views'] ?: 0;
                            $pageViewsMobile = $value['page_views_mobile'] ?: 0;
                            $pageViewsTotal  = $value['page_views_total'] ?: 0;

                            if (!$cateRank && $avgStar <= 0 && !$reviewsCount && !$sessions && !$sessionsMobile &&
                                !$sessionsTotal && !$pageViews && !$pageViewsMobile && !$pageViewsTotal) {
                                continue;
                            }


                            $skuInfo = DB::table('self_sku')->where('asin', $value['asins'][0]['asin'])->first();
                            $insert  = [
                                'fasin'             => $value['parent_asins'][0]['parent_asin'],
                                'asin'              => $value['asins'][0]['asin'],
                                'cate_rank'         => $cateRank,
                                'avg_star'          => $avgStar,
                                'reviews_count'     => $reviewsCount,
                                'sessions'          => $sessions,
                                'sessions_mobile'   => $sessionsMobile,
                                'sessions_total'    => $sessionsTotal,
                                'page_views'        => $pageViews,
                                'page_views_mobile' => $pageViewsMobile,
                                'page_views_total'  => $pageViewsTotal,
                                'date'              => $date,
                                'sid'               => $seller['sid'],
                                'shop_id'           => $shopIdKeyByLx[$seller['sid']] ?? 0,
                                'sku_id'            => $skuInfo ? $skuInfo->id : 0,
                                'custom_sku_id'     => $skuInfo ? $skuInfo->custom_sku_id : 0,
                                'spu_id'            => $skuInfo ? $skuInfo->spu_id : 0,
                            ];

                            $info = DB::table('lingxing_product_expression')
                                ->where('fasin', $value['parent_asins'][0]['parent_asin'])
                                ->where('asin', $value['asins'][0]['asin'])
                                ->where('date', $date)
                                ->where('sid', $seller['sid'])
                                ->first();

                            if ($info) {
                                $id   = $info->id;
                                $info = json_decode(json_encode($info), true);
                                $diff = array_diff($insert, $info);
                                if (count($diff) > 0) {
                                    $diff['update_time'] = date('Y-m-d H:i:s');
                                    DB::table('lingxing_product_expression')->where('id', $id)->update($diff);
                                }
                            } else {
                                $insert['create_time'] = date('Y-m-d H:i:s');
                                DB::table('lingxing_product_expression')->insert($insert);
                            }
                        }
                        !$totalPage && $totalPage = ceil($res['data']['total'] / $length);
                        $p++;
                    } else {
                        self::writeLog('lxProductExpressionAsin', $params, $res);
                    }
                } catch (\Exception $exception) {
                    self::writeLog(
                        'lxProductExpressionAsin',
                        $params,
                        ['line' => $exception->getLine(), 'msg' => $exception->getMessage()]
                    );
                }
            } while ($p <= $totalPage);
        }
    }


    //获取亚马逊订单
    public function getOrder()
    {
        $type = $this->argument('p1');//1:拉取新订单 2:更新订单

        if ($type == 1) {
            $startDate = strtotime(date('Y-m-d H:i:s', strtotime('-2 days')));
            $endDate   = time();
            $dateType  = 'global_purchase_time';
        } else {
            $startDate = strtotime(date('Y-m-d H:i:s', strtotime('-2 days')));
            $endDate   = time();
            $dateType  = 'update_time';
        }

        $shopIdKeyByLx = DB::table('shop')->where('lingxing_id', '>', 0)->pluck('id', 'lingxing_id');
        //领星多平台店铺id对应的亚马逊店铺id
        $mpSellerListRes = (new LingXingApiController())->getMpSellerList(0, 200, [10001]);
        $sidKeyByStoreId = array_column($mpSellerListRes['data']['list'], 'sid', 'store_id');


        $p         = 1;    //页码
        $length    = 100;  //页数
        $totalPage = 0;    //总页数

        do {
            echo $totalPage . '___' . $p . PHP_EOL;


            $params = [
                'offset'        => ($p - 1) * $length,
                'length'        => $length,
                'start_time'    => $startDate,
                'end_time'      => $endDate,
                'date_type'     => $dateType,
                'platform_code' => [10001],
            ];


            try {
                $orderList = (new LingXingApiController())->getOrders($params);

                if ($orderList['code'] != 1) {
                    self::writeLog('lxGetOrder', $params, $orderList);
                    continue;
                }

                foreach ($orderList['data']['list'] as $order) {

                    if ($order['split_type'] != 1) continue;//不是原始单的先跳过

                    $sid    = $sidKeyByStoreId[$order['store_id']] ?? 0;
                    $shopId = $shopIdKeyByLx[$sid] ?? 0;

                    $orderStatusKeyByOrderNo = array_column($order['platform_info'], 'status', 'platform_order_no');//获取订单状态

                    $data = [
                        'global_order_no'         => $order['global_order_no'],
                        'store_id'                => $order['store_id'],
                        'amazon_order_id'         => $order['platform_info'][0]['platform_order_no'],
                        'shop_id'                 => $shopId,
                        'sid'                     => $sid,
                        'order_from_name'         => $order['order_from_name'],
                        'delivery_type'           => $order['delivery_type'],
                        'split_type'              => $order['split_type'],
                        'status'                  => $order['status'],
                        'order_status'            => $orderStatusKeyByOrderNo[$order['platform_info'][0]['platform_order_no']],
                        'amount_currency'         => $order['amount_currency'],
                        'buyer_no'                => $order['buyers_info']['buyer_no'] ?: '',
                        'buyer_name'              => $order['buyers_info']['buyer_name'] ?: '',
                        'buyer_email'             => $order['buyers_info']['buyer_email'] ?: '',
                        'buyer_note'              => $order['buyers_info']['buyer_note'] ?: '',
                        'receiver_name'           => $order['address_info']['receiver_name'] ?: '',
                        'receiver_mobile'         => $order['address_info']['receiver_mobile'] ?: '',
                        'receiver_tel'            => $order['address_info']['receiver_tel'] ?: '',
                        'receiver_country_code'   => $order['address_info']['receiver_country_code'] ?: '',
                        'city'                    => $order['address_info']['city'] ?: '',
                        'state_or_region'         => $order['address_info']['state_or_region'] ?: '',
                        'address_line1'           => $order['address_info']['address_line1'] ?: '',
                        'address_line2'           => $order['address_info']['address_line2'] ?: '',
                        'address_line3'           => $order['address_info']['address_line3'] ?: '',
                        'district'                => $order['address_info']['district'] ?: '',
                        'postal_code'             => $order['address_info']['postal_code'] ?: '',
                        'doorplate_no'            => $order['address_info']['doorplate_no'] ?: '',
                        'global_purchase_time'    => $order['global_purchase_time'],
                        'global_payment_time'     => $order['global_payment_time'],
                        'global_delivery_time'    => $order['global_delivery_time'],
                        'global_latest_ship_time' => $order['global_latest_ship_time'],
                        'global_cancel_time'      => $order['global_cancel_time'],
                        'lx_update_time'          => $order['update_time'],
                        'create_time'             => date('Y-m-d H:i:s'),
                        'update_time'             => date('Y-m-d H:i:s'),
                    ];

                    $info = DB::table('lingxing_amazon_order')
                        ->where('amazon_order_id', $order['platform_info'][0]['platform_order_no'])
                        ->where('sid', $sid)
                        ->first();

                    if ($type == 1 && !$info) {
                        $id = DB::table('lingxing_amazon_order')->insertGetId($data);

                        foreach ($order['item_info'] as $item) {

                            $userId      = 0;
                            $customSkuId = 0;
                            $spuId       = 0;
                            $skuId       = 0;
                            $spu         = '';

                            $skuInfo = DB::table('self_sku')->where('sku', $item['msku'])->orWhere('old_sku', $item['msku'])->first();

                            if ($skuInfo) {
                                $spuId       = $skuInfo->spu_id;
                                $skuId       = $skuInfo->id;
                                $spu         = $skuInfo->spu;
                                $userId      = $skuInfo->user_id;
                                $customSkuId = $skuInfo->custom_sku_id;
                            }
                            DB::table('lingxing_amazon_order_item')->insert([
                                'lingxing_amazon_order_id' => $id,
                                'amazon_order_id'          => $item['platform_order_no'],
                                'order_item_no'            => $item['order_item_no'],
                                'order_status'             => $orderStatusKeyByOrderNo[$item['platform_order_no']],
                                'sid'                      => $sid,
                                'shop_id'                  => $shopId,
                                'quantity'                 => $item['quantity'],
                                'msku'                     => $item['msku'],
                                'local_sku'                => $item['local_sku'],
                                'product_no'               => $item['product_no'],
                                'local_product_name'       => $item['local_product_name'],
                                'unit_price_amount'        => $item['unit_price_amount'],
                                'item_price_amount'        => $item['item_price_amount'],
                                'customer_shipping_amount' => $item['customer_shipping_amount'],
                                'discount_amount'          => $item['discount_amount'],
                                'customer_tip_amount'      => $item['customer_tip_amount'],
                                'tax_amount'               => $item['tax_amount'],
                                'transaction_fee_amount'   => $item['transaction_fee_amount'],
                                'platform_status'          => $item['platform_status'],
                                'global_purchase_time'     => $order['global_purchase_time'],
                                'user_id'                  => $userId,
                                'custom_sku_id'            => $customSkuId,
                                'spu_id'                   => $spuId,
                                'sku_id'                   => $skuId,
                                'spu'                      => $spu,
                                'create_time'              => date('Y-m-d H:i:s'),
                                'update_time'              => date('Y-m-d H:i:s'),
                            ]);
                        }
                    } elseif ($type == 2 && $info) {
                        unset($data['create_time']);
                        $info = json_decode(json_encode($info), true);
                        $diff = array_diff($data, $info);

                        if (count($diff) > 1) {
                            DB::table('lingxing_amazon_order')->where('id', $info['id'])->update($diff);
                        }

                        if (isset($diff['order_status'])) {
                            DB::table('lingxing_amazon_order_item')->where('lingxing_amazon_order_id', $info['id'])->update([
                                'order_status' => $diff['order_status']
                            ]);
                        }
                    }
                }

                !$totalPage && $totalPage = ceil($orderList['data']['total'] / $length);
                $p++;
            } catch (\Exception $exception) {
                self::writeLog(
                    'lxGetOrder',
                    $params,
                    ['line' => $exception->getLine(), 'msg' => $exception->getMessage()]
                );
            }


        } while ($p <= $totalPage);


        exit('程序结束');
    }

    //更新亚马逊订单状态
    public function updateOrder(){
        $orderData = DB::table('lingxing_amazon_order')->whereIn('order_status',['pending','Unshipped'])->select('amazon_order_id','id')->get()->toArray();
        if(!empty($orderData)){
            $con = new LingXingApiController();
            foreach ($orderData as $v){
                $params = [
                    'order_id' => $v->amazon_order_id
                ];
                $orderList = $con->getOrdersDetail($params);


                self::writeLog('Console/Commands/Lingxing/updateOrder',$params, $orderList);

                $update = DB::table('lingxing_amazon_order')->where('amazon_order_id', $v->amazon_order_id)->update(['order_status'=>$orderList['data'][0]['order_status']]);
                $update1 = DB::table('lingxing_amazon_order_item')->where('amazon_order_id', $v->amazon_order_id)->update(['order_status'=>$orderList['data'][0]['order_status']]);
                if($update){
                    echo '订单号:'.$v->amazon_order_id.' 更新表amazon_order状态:'.$orderList['data'][0]['order_status']. PHP_EOL;
                }
                if($update1){
                    echo '订单号:'.$v->amazon_order_id.' 更新表amazon_order_item状态:'.$orderList['data'][0]['order_status']. PHP_EOL;
                }

            }
        }

        exit('程序结束');


    }

    //获取亚马逊订单
//    public function getOrderBac()
//    {
//        $type = $this->argument('p1');//1:拉取新订单 2:更新订单
//
//        if ($type == 1) {
//            $startDate = date('Y-m-d H:i:s', strtotime('-1 days'));
//            $endDate   = date('Y-m-d H:i:s');
//        } else {
//            $startDate = date('Y-m-d H:i:s', strtotime('-1 hours'));
//            $endDate   = date('Y-m-d H:i:s');
//        }
//
//
//        $shopIdKeyByLx = DB::table('shop')->where('lingxing_id', '>', 0)->pluck('id', 'lingxing_id');
//
//        $p         = 1;    //页码
//        $length    = 100;  //页数
//        $totalPage = 0;    //总页数
//
//        do {
//            echo $totalPage . '___' . $p . PHP_EOL;
//
//
//            $params = [
//                'offset'                 => ($p - 1) * $length,
//                'length'                 => $length,
//                'start_date'             => $startDate,
//                'end_date'               => $endDate,
//                'date_type'              => $type,//1 订购时间【站点时间】 2 订单修改时间【北京时间】
//                'sort_desc_by_date_type' => 2,//降序排序
//            ];
//
//
//            try {
//                $orderList = (new LingXingApiController())->getMwsOrders($params);
//                if ($orderList['code'] != 0) {
//                    self::writeLog(
//                        'lxGetOrder',
//                        $params,
//                        $orderList
//                    );
//                    continue;
//                }
//
//                foreach ($orderList['data'] as $order) {
//                    $shopId = $shopIdKeyByLx[$order['sid']] ?? 0;
//
//                    $data = [
//                        'amazon_order_id'           => $order['amazon_order_id'],
//                        'shop_id'                   => $shopIdKeyByLx[$order['sid']] ?? 0,
//                        'sid'                       => $order['sid'],
//                        'seller_name'               => $order['seller_name'],
//                        'order_status'              => $order['order_status'],
//                        'order_total_amount'        => $order['order_total_amount'],
//                        'fulfillment_channel'       => $order['fulfillment_channel'],
//                        'postal_code'               => $order['postal_code'],
//                        'phone'                     => $order['phone'],
//                        'name'                      => $order['name'],
//                        'address'                   => $order['address'],
//                        'buyer_name'                => $order['buyer_name'],
//                        'buyer_email'               => $order['buyer_email'],
//                        'is_return'                 => $order['is_return'],
//                        'is_assessed'               => $order['is_assessed'],
//                        'is_mcf_order'              => $order['is_mcf_order'],
//                        'is_replaced_order'         => $order['is_replaced_order'],
//                        'is_replacement_order'      => $order['is_replacement_order'],
//                        'is_return_order'           => $order['is_return_order'],
//                        'order_total_currency_code' => $order['order_total_currency_code'],
//                        'sales_channel'             => $order['sales_channel'],
//                        'tracking_number'           => $order['tracking_number'],
//                        'refund_amount'             => $order['refund_amount'],
//                        'purchase_date_local'       => $order['purchase_date_local'],
//                        'purchase_date_local_utc'   => $order['purchase_date_local_utc'],
//                        'shipment_date'             => $order['shipment_date'],
//                        'shipment_date_utc'         => $order['shipment_date_utc'],
//                        'shipment_date_local'       => $order['shipment_date_local'],
//                        'last_update_date'          => $order['last_update_date'],
//                        'last_update_date_utc'      => $order['last_update_date_utc'],
//                        'posted_date'               => $order['posted_date'],
//                        'posted_date_utc'           => $order['posted_date_utc'],
//                        'purchase_date'             => $order['purchase_date'],
//                        'purchase_date_utc'         => $order['purchase_date_utc'],
//                        'earliest_ship_date'        => $order['earliest_ship_date'],
//                        'earliest_ship_date_utc'    => $order['earliest_ship_date_utc'],
//                        'gmt_modified'              => $order['gmt_modified'],
//                        'gmt_modified_utc'          => $order['gmt_modified_utc'],
//                        'hide_time'                 => $order['hide_time'],
//                        'create_time'               => date('Y-m-d H:i:s'),
//                        'update_time'               => date('Y-m-d H:i:s'),
//                    ];
//
//                    $info = DB::table('lingxing_amazon_order')
//                        ->where('amazon_order_id', $order['amazon_order_id'])
//                        ->where('sid', $order['sid'])
//                        ->first();
//
//                    if ($type == 1 && !$info) {
//
//                        $id = DB::table('lingxing_amazon_order')->insertGetId($data);
//
//                        foreach ($order['item_list'] as $item) {
//
//                            $userId      = 0;
//                            $customSkuId = 0;
//                            $spuId       = 0;
//                            $skuId       = 0;
//                            $spu         = '';
//
//                            $skuInfo = DB::table('self_sku')->where('sku', $item['seller_sku'])->orWhere('old_sku', $item['seller_sku'])->first();
//
//                            if ($skuInfo) {
//                                $spuId       = $skuInfo->spu_id;
//                                $skuId       = $skuInfo->id;
//                                $spu         = $skuInfo->spu;
//                                $userId      = $skuInfo->user_id;
//                                $customSkuId = $skuInfo->custom_sku_id;
//                            }
//
//                            DB::table('lingxing_amazon_order_item')->insert([
//                                'lingxing_amazon_order_id' => $id,
//                                'amazon_order_id'          => $order['amazon_order_id'],
//                                'sid'                      => $order['sid'],
//                                'shop_id'                  => $shopId,
//                                'asin'                     => $item['asin'],
//                                'quantity_ordered'         => $item['quantity_ordered'],
//                                'seller_sku'               => $item['seller_sku'],
//                                'local_sku'                => $item['local_sku'] ?? '',
//                                'local_name'               => $item['local_name'] ?: '',
//                                'user_id'                  => $userId,
//                                'custom_sku_id'            => $customSkuId,
//                                'spu_id'                   => $spuId,
//                                'sku_id'                   => $skuId,
//                                'spu'                      => $spu,
//                                'create_time'              => date('Y-m-d H:i:s'),
//                                'update_time'              => date('Y-m-d H:i:s'),
//                            ]);
//                        }
//                    } elseif ($type == 2 && $info) {
//                        unset($data['create_time']);
//                        $info = json_decode(json_encode($info), true);
//                        $diff = array_diff($data, $info);
//                        if (count($diff) > 1) {
//                            DB::table('lingxing_amazon_order')->where('id', $info['id'])->update($diff);
//                        }
//                    }
//                }
//
//                !$totalPage && $totalPage = ceil($orderList['total'] / $length);
//                $p++;
//            } catch (\Exception $exception) {
//                self::writeLog(
//                    'lxGetOrder',
//                    $params,
//                    ['line' => $exception->getLine(), 'msg' => $exception->getMessage()]
//                );
//            }
//
//
//        } while ($p <= $totalPage);
//
//
//        exit('程序结束');
//    }


    //广告活动报表
    public function adCampaignReports()
    {
        //获取领星所有店铺
        $sellerList = (new LingXingApiController())->getSellerList();
        if ($sellerList['code'] != 0) {
            exit(json_encode($sellerList, JSON_UNESCAPED_UNICODE));
        }


        //先更新一下广告活动和广告组合数据库
        $this->getAdPortfolios($sellerList);

        $dayNum = $this->argument('p1');

        print_r($dayNum);
        $dateArr = [];
        for ($i = 1; $i <= $dayNum; $i++) {
            $dateArr[] = date('Y-m-d', strtotime("-$i days"));
        }
        print_r($dateArr);
        $shopIdKeyBySid = DB::table('shop')->where('lingxing_id', '>', 0)->pluck('id', 'lingxing_id');

        foreach ($dateArr as $date) {
            $this->adCampaignReportsFun($date, $sellerList, $shopIdKeyBySid);
        }


        exit('程序结束');

    }

    //广告活动报表
    public function adCampaignReportsFun($date, $sellerList, $shopIdKeyBySid)
    {

        $count = count($sellerList['data']);


        //sp广告活动报表
        $n = 1;
        foreach ($sellerList['data'] as $seller) {

            echo $count . '___' . $n . PHP_EOL;
            $n++;

            $sid   = $seller['sid'];
            $lxApi = new LingXingApiController();


            $p         = 1;    //页码
            $length    = 100;  //页数
            $totalPage = 0;    //总页数

            do {

                $spCampaignReportsList = $lxApi->spCampaignReports([
                    'sid'         => $sid,
                    'report_date' => $date,
                    'offset'      => ($p - 1) * $length,
                    'length'      => $length,
                ]);

                $input_param = [
                    'sid'         => $sid,
                    'report_date' => $date,
                    'offset'      => ($p - 1) * $length,
                    'length'      => $length,
                ];

                self::writeLog('Console/Commands/Lingxing/spCampaignReports',$input_param, $spCampaignReportsList);

                if ($spCampaignReportsList['code'] != 0) {
                    continue;
                }

                foreach ($spCampaignReportsList['data'] as $spCampaignReports) {

                    $insert = [
                        'sid'            => $sid,
                        'shop_id'        => $shopIdKeyBySid[$sid] ?? 0,
                        'targeting_type' => $spCampaignReports['targeting_type'] ?: '',
                        'impressions'    => $spCampaignReports['impressions'],
                        'clicks'         => $spCampaignReports['clicks'],
                        'cost'           => $spCampaignReports['cost'],
                        'report_date'    => $spCampaignReports['report_date'],
                        'profile_id'     => $spCampaignReports['profile_id'],
                        'campaign_id'    => $spCampaignReports['campaign_id'],
                        'same_orders'    => $spCampaignReports['same_orders'],
                        'orders'         => $spCampaignReports['orders'],
                        'same_sales'     => $spCampaignReports['same_sales'],
                        'sales'          => $spCampaignReports['sales'],
                        'units'          => $spCampaignReports['units'],
                        'same_units'     => $spCampaignReports['same_units'],
                        'ad_type'        => 'sp',
                    ];

                    $info = DB::table('lingxing_ad_campaign_report')
                        ->where('report_date', $spCampaignReports['report_date'])
                        ->where('campaign_id', $spCampaignReports['campaign_id'])
                        ->first();

                    if ($info) {
                        $id   = $info->id;
                        $info = json_decode(json_encode($info), true);
                        $diff = array_diff($insert, $info);
                        if (count($diff) > 0) {
                            $diff['update_time'] = date('Y-m-d H:i:s');
                            DB::table('lingxing_ad_campaign_report')->where('id', $id)->update($diff);
                        }
                    } else {
                        $insert['create_time'] = date('Y-m-d H:i:s');
                        DB::table('lingxing_ad_campaign_report')->insert($insert);
                    }

                }


                $totalPage = ceil($spCampaignReportsList['total'] / $length);
                $p++;
            } while ($p <= $totalPage);

        }

        //sb广告活动报表
        $n = 1;
        foreach ($sellerList['data'] as $seller) {
            echo $count . '___' . $n . PHP_EOL;
            $n++;


            $sid   = $seller['sid'];
            $lxApi = new LingXingApiController();


            $p         = 1;    //页码
            $length    = 100;  //页数
            $totalPage = 0;    //总页数

            do {

                $hsaCampaignReportsList = $lxApi->hsaCampaignReports([
                    'sid'         => $sid,
                    'report_date' => $date,
                    'offset'      => ($p - 1) * $length,
                    'length'      => $length,
                ]);

                $input_param2 = [
                    'sid' => $sid,
                    'report_date' => $date,
                    'offset' => ($p - 1) * $length,
                    'length' => $length,
                ];

                self::writeLog('Console/Commands/Lingxing/hsaCampaignReports',$input_param2, $hsaCampaignReportsList);


                if ($hsaCampaignReportsList['code'] != 0) {
                    continue;
                }


                foreach ($hsaCampaignReportsList['data'] as $hsaCampaignReports) {
                    $insert = [
                        'sid'         => $sid,
                        'shop_id'     => $shopIdKeyBySid[$sid] ?? 0,
                        'impressions' => $hsaCampaignReports['impressions'],
                        'clicks'      => $hsaCampaignReports['clicks'],
                        'cost'        => $hsaCampaignReports['cost'],
                        'report_date' => $hsaCampaignReports['report_date'],
                        'profile_id'  => $hsaCampaignReports['profile_id'],
                        'campaign_id' => $hsaCampaignReports['campaign_id'],
                        'same_orders' => $hsaCampaignReports['same_orders'],
                        'orders'      => $hsaCampaignReports['orders'],
                        'same_sales'  => $hsaCampaignReports['same_sales'],
                        'sales'       => $hsaCampaignReports['sales'],
                        'ad_type'     => 'sb',
                    ];

                    $info = DB::table('lingxing_ad_campaign_report')
                        ->where('report_date', $hsaCampaignReports['report_date'])
                        ->where('campaign_id', $hsaCampaignReports['campaign_id'])
                        ->first();

                    if ($info) {
                        $id   = $info->id;
                        $info = json_decode(json_encode($info), true);
                        $diff = array_diff($insert, $info);
                        if (count($diff) > 0) {
                            $diff['update_time'] = date('Y-m-d H:i:s');
                            DB::table('lingxing_ad_campaign_report')->where('id', $id)->update($diff);
                        }
                    } else {
                        $insert['create_time'] = date('Y-m-d H:i:s');
                        DB::table('lingxing_ad_campaign_report')->insert($insert);
                    }
                }


                $totalPage = ceil($hsaCampaignReportsList['total'] / $length);
                $p++;
            } while ($p <= $totalPage);

        }

        //SD广告活动报表
        $n = 1;
        foreach ($sellerList['data'] as $seller) {
            echo $count . '___' . $n . PHP_EOL;
            $n++;


            $sid   = $seller['sid'];
            $lxApi = new LingXingApiController();


            $p         = 1;    //页码
            $length    = 100;  //页数
            $totalPage = 0;    //总页数

            do {

                $sdCampaignReportsList = $lxApi->sdCampaignReports([
                    'sid'         => $sid,
                    'report_date' => $date,
                    'offset'      => ($p - 1) * $length,
                    'length'      => $length,
                ]);

                $input_param3 = [
                    'sid' => $sid,
                    'report_date' => $date,
                    'offset' => ($p - 1) * $length,
                    'length' => $length,
                ];

                self::writeLog('Console/Commands/Lingxing/sdCampaignReports',$input_param3, $sdCampaignReportsList);


                if ($sdCampaignReportsList['code'] != 0) {
                    continue;
                }

                foreach ($sdCampaignReportsList['data'] as $sdCampaignReports) {
                    $insert = [
                        'sid'            => $sid,
                        'shop_id'        => $shopIdKeyBySid[$sid] ?? 0,
                        'targeting_type' => $sdCampaignReports['tactic'],
                        'impressions'    => $sdCampaignReports['impressions'],
                        'clicks'         => $sdCampaignReports['clicks'],
                        'cost'           => $sdCampaignReports['cost'],
                        'report_date'    => $sdCampaignReports['report_date'],
                        'profile_id'     => $sdCampaignReports['profile_id'],
                        'campaign_id'    => $sdCampaignReports['campaign_id'],
                        'same_orders'    => $sdCampaignReports['same_orders'],
                        'orders'         => $sdCampaignReports['orders'],
                        'same_sales'     => $sdCampaignReports['same_sales'],
                        'sales'          => $sdCampaignReports['sales'],
                        'units'          => $sdCampaignReports['units'],
                        'ad_type'        => 'sd',
                        'create_time'    => date('Y-m-d H:i:s')
                    ];

                    $info = DB::table('lingxing_ad_campaign_report')
                        ->where('report_date', $sdCampaignReports['report_date'])
                        ->where('campaign_id', $sdCampaignReports['campaign_id'])
                        ->first();

                    if ($info) {
                        $id   = $info->id;
                        $info = json_decode(json_encode($info), true);
                        $diff = array_diff($insert, $info);
                        if (count($diff) > 0) {
                            $diff['update_time'] = date('Y-m-d H:i:s');
                            DB::table('lingxing_ad_campaign_report')->where('id', $id)->update($diff);
                        }
                    } else {
                        $insert['create_time'] = date('Y-m-d H:i:s');
                        DB::table('lingxing_ad_campaign_report')->insert($insert);
                    }
                }


                $totalPage = ceil($sdCampaignReportsList['total'] / $length);
                $p++;
            } while ($p <= $totalPage);

        }

        /*
        //SB广告的投放报告
        $n = 1;
        foreach ($sellerList['data'] as $seller) {

            echo $count . '___' . $n . PHP_EOL;
            $n++;

            $sid   = $seller['sid'];
            $lxApi = new LingXingApiController();


            $p         = 1;    //页码
            $length    = 100;  //页数
            $totalPage = 0;    //总页数

            do {

                $listHsaTargetingReportList = $lxApi->listHsaTargetingReport([
                    'sid'            => $sid,
                    'sponsored_type' => 'ALL',
                    'target_type'    => 'keyword',
                    'report_date'    => $date,
                    'offset'         => ($p - 1) * $length,
                    'length'         => $length,
                ]);

                if ($listHsaTargetingReportList['code'] != 0) {
                    continue;
                }


                foreach ($listHsaTargetingReportList['data'] as $listHsaTargetingReport) {
                    DB::table('lingxing_ad_sb_targeting_report')->insert([
                        'sid'           => $sid,
                        'shop_id'       => $shopIdKeyBySid[$sid] ?? 0,
                        'report_date'   => $listHsaTargetingReport['report_date'],
                        'creative_type' => $listHsaTargetingReport['creative_type'] ?: '',
                        'profile_id'    => $listHsaTargetingReport['profile_id'],
                        'keyword_id'    => $listHsaTargetingReport['keyword_id'],
                        'campaign_id'   => $listHsaTargetingReport['campaign_id'],
                        'ad_group_id'   => $listHsaTargetingReport['ad_group_id'],
                        'impressions'   => $listHsaTargetingReport['impressions'],
                        'clicks'        => $listHsaTargetingReport['clicks'],
                        'cost'          => $listHsaTargetingReport['cost'],
                        'same_orders'   => $listHsaTargetingReport['same_orders'],
                        'orders'        => $listHsaTargetingReport['orders'],
                        'same_sales'    => $listHsaTargetingReport['same_sales'],
                        'sales'         => $listHsaTargetingReport['sales'],
                        'create_time'   => date('Y-m-d H:i:s')
                    ]);
                }


                $totalPage = ceil($listHsaTargetingReportList['total'] / $length);
                $p++;
            } while ($p <= $totalPage);

        }

        //SP广告商品报表
        $n = 1;
        foreach ($sellerList['data'] as $seller) {

            echo $count . '___' . $n . PHP_EOL;
            $n++;

            $sid   = $seller['sid'];
            $lxApi = new LingXingApiController();


            $p         = 1;    //页码
            $length    = 100;  //页数
            $totalPage = 0;    //总页数

            do {

                $spProductAdReportsList = $lxApi->spProductAdReports([
                    'sid'            => $sid,
                    'sponsored_type' => 'ALL',
                    'target_type'    => 'keyword',
                    'report_date'    => $date,
                    'offset'         => ($p - 1) * $length,
                    'length'         => $length,
                ]);

                if ($spProductAdReportsList['code'] != 0) {
                    continue;
                }

                foreach ($spProductAdReportsList['data'] as $spProductAdReports) {
                    DB::table('lingxing_ad_product_reports')->insert([
                        'sid'         => $sid,
                        'shop_id'     => $shopIdKeyBySid[$sid] ?? 0,
                        'report_date' => $spProductAdReports['report_date'],
                        'asin'        => $spProductAdReports['asin'] ?: '',
                        'sku'         => $spProductAdReports['sku'] ?: '',
                        'profile_id'  => $spProductAdReports['profile_id'],
                        'ad_id'       => $spProductAdReports['ad_id'],
                        'campaign_id' => $spProductAdReports['campaign_id'],
                        'ad_group_id' => $spProductAdReports['ad_group_id'],
                        'impressions' => $spProductAdReports['impressions'],
                        'clicks'      => $spProductAdReports['clicks'],
                        'cost'        => $spProductAdReports['cost'],
                        'same_orders' => $spProductAdReports['same_orders'],
                        'orders'      => $spProductAdReports['orders'],
                        'same_sales'  => $spProductAdReports['same_sales'],
                        'sales'       => $spProductAdReports['sales'],
                        'units'       => $spProductAdReports['units'],
                        'same_units'  => $spProductAdReports['same_units'],
                        'ad_type'     => 'sp',
                        'create_time' => date('Y-m-d H:i:s')
                    ]);
                }

                $totalPage = ceil($spProductAdReportsList['total'] / $length);
                $p++;
            } while ($p <= $totalPage);
        }

        //SD广告商品报表'
        $n = 1;
        foreach ($sellerList['data'] as $seller) {

            echo $count . '___' . $n . PHP_EOL;
            $n++;

            $sid   = $seller['sid'];
            $lxApi = new LingXingApiController();


            $p         = 1;    //页码
            $length    = 100;  //页数
            $totalPage = 0;    //总页数

            do {

                $sdProductAdReportsList = $lxApi->sdProductAdReports([
                    'sid'            => $sid,
                    'sponsored_type' => 'ALL',
                    'target_type'    => 'keyword',
                    'report_date'    => $date,
                    'offset'         => ($p - 1) * $length,
                    'length'         => $length,
                ]);

                if ($sdProductAdReportsList['code'] != 0) {
                    continue;
                }

                foreach ($sdProductAdReportsList['data'] as $sdProductAdReports) {
                    DB::table('lingxing_ad_product_reports')->insert([
                        'sid'         => $sid,
                        'shop_id'     => $shopIdKeyBySid[$sid] ?? 0,
                        'report_date' => $sdProductAdReports['report_date'],
                        'asin'        => $sdProductAdReports['asin'] ?: '',
                        'sku'         => $sdProductAdReports['sku'] ?: '',
                        'profile_id'  => $sdProductAdReports['profile_id'],
                        'ad_id'       => $sdProductAdReports['ad_id'],
                        'campaign_id' => $sdProductAdReports['campaign_id'],
                        'ad_group_id' => $sdProductAdReports['ad_group_id'],
                        'impressions' => $sdProductAdReports['impressions'],
                        'clicks'      => $sdProductAdReports['clicks'],
                        'cost'        => $sdProductAdReports['cost'],
                        'same_orders' => $sdProductAdReports['same_orders'],
                        'orders'      => $sdProductAdReports['orders'],
                        'same_sales'  => $sdProductAdReports['same_sales'],
                        'sales'       => $sdProductAdReports['sales'],
                        'units'       => $sdProductAdReports['units'],
//                        'same_units'  => $sdProductAdReports['same_units'],
                        'ad_type'     => 'sd',
                        'create_time' => date('Y-m-d H:i:s')
                    ]);
                }

                $totalPage = ceil($sdProductAdReportsList['total'] / $length);
                $p++;
            } while ($p <= $totalPage);
        }

        */
    }

    //获取广告组合名称
    public function getAdPortfolios($sellerList)
    {
        $count = count($sellerList['data']);

        $portfoliosNameKeyById = [];

        //查询广告组合
        $n = 1;
        foreach ($sellerList['data'] as $seller) {

            echo $count . '___' . $n . PHP_EOL;
            $n++;

            $sid   = $seller['sid'];
            $lxApi = new LingXingApiController();


            $p         = 1;    //页码
            $length    = 100;  //页数
            $totalPage = 0;    //总页数

            do {

                $portfoliosList = $lxApi->portfolios([
                    'sid'    => $sid,
                    'offset' => ($p - 1) * $length,
                    'length' => $length,
                ]);

                $input_param4 = [
                    'sid'    => $sid,
                    'offset' => ($p - 1) * $length,
                    'length' => $length,
                ];
                self::writeLog('Console/Commands/Lingxing/getAdPortfolios',$input_param4, $portfoliosList);

                if ($portfoliosList['code'] != 0) {
                    continue;
                }

                foreach ($portfoliosList['data'] as $portfolios) {
                    $portfolioId = $portfolios['portfolio_id'];

                    $portfoliosNameKeyById[$portfolioId] = $portfolios['name'];

                    $info = DB::table('lingxing_ad_portfolios')->where('portfolio_id', $portfolioId)->first();

                    $insert = [
                        'portfolio_id'      => $portfolioId,
                        'profile_id'        => $portfolios['profile_id'],
                        'name'              => $portfolios['name'],
                        'budget'            => $portfolios['budget'],
                        'in_budget'         => $portfolios['in_budget'],
                        'state'             => $portfolios['state'],
                        'creation_date'     => $portfolios['creation_date'],
                        'last_updated_date' => $portfolios['last_updated_date'],
                        'serving_status'    => $portfolios['serving_status'],
                    ];

                    if ($info) {
                        $id   = $info->id;
                        $info = json_decode(json_encode($info), true);
                        $diff = array_diff($insert, $info);
                        if (count($diff) > 0) {
                            DB::table('lingxing_ad_portfolios')->where('id', $id)->update($diff);
                        }
                    } else {
                        $insert['create_time'] = date('Y-m-d H:i:s');
                        DB::table('lingxing_ad_portfolios')->insert($insert);
                    }


                }

                $totalPage = ceil($portfoliosList['total'] / $length);
                $p++;
            } while ($p <= $totalPage);
        }

        //查询广告活动
        $campaignArr = ['sp' => 'spCampaigns', 'sb' => 'hsaCampaigns', 'sd' => 'sdCampaigns'];
        foreach ($campaignArr as $adType => $campaign) {
            $n = 1;
            foreach ($sellerList['data'] as $seller) {

                echo $count . '___' . $n . PHP_EOL;
                $n++;

                $sid   = $seller['sid'];
                $lxApi = new LingXingApiController();


                $p         = 1;    //页码
                $length    = 100;  //页数
                $totalPage = 0;    //总页数

                do {

                    $res = $lxApi->$campaign([
                        'sid'    => $sid,
                        'offset' => ($p - 1) * $length,
                        'length' => $length,
                    ]);

                    if ($res['code'] != 0) {
                        continue;
                    }

                    $input_param5 = [
                        'sid'    => $sid,
                        'offset' => ($p - 1) * $length,
                        'length' => $length,
                    ];
                    self::writeLog('Console/Commands/Lingxing/'.$campaign,$input_param5, $res);

                    foreach ($res['data'] as $value) {
                        if($value['campaign_id']) {
                            $info = DB::table('lingxing_ad_campaign')->where('campaign_id', $value['campaign_id'])->first();

                            $insert = [
                                'campaign_id' => $value['campaign_id'],
                                'name' => $value['name'],
                                'profile_id' => $value['profile_id'],
                                'portfolio_id' => $value['portfolio_id'] ?: 0,
                                'portfolio_name' => $portfoliosNameKeyById[$value['portfolio_id']] ?? '',
                                'ad_type' => $adType,
                            ];

                            if ($info) {
                                $id = $info->id;
                                $info = json_decode(json_encode($info), true);
                                $diff = array_diff($insert, $info);
                                if (count($diff) > 0) {
                                    DB::table('lingxing_ad_campaign')->where('id', $id)->update($diff);
                                }
                            } else {
                                $insert['create_time'] = date('Y-m-d H:i:s');
                                DB::table('lingxing_ad_campaign')->insert($insert);
                            }
                        }
                    }

                    $totalPage = ceil($res['total'] / $length);
                    $p++;
                } while ($p <= $totalPage);
            }
        }
    }


    //导出Fasin和负责人对应表
    public function exportTmp()
    {
        $amazonFasinList = DB::table('amazon_fasin')->select('fasin', 'user_id')->get();

        $userIds  = $amazonFasinList->pluck('user_id');
        $userList = DB::table('users')->whereIn('id', $userIds)->pluck('account', 'id');

        $exportArr = [];

        foreach ($amazonFasinList as $amazonFasin) {
            $exportArr[] = [
                'fasin' => $amazonFasin->fasin,
                'user'  => $userList[$amazonFasin->user_id],

            ];
        }

        $expCellName = [
            ['fasin', 'fasin'],
            ['user', '负责人'],
        ];

        $this->exportExcel("fasin.xlsx", $expCellName, $exportArr);
        exit('程序结束');
    }

    public function exportMskuSize()
    {

        ini_set("memory_limit", "256M");

        $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        $objReader->setReadDataOnly(true);
        $PHPExcel = $objReader->load('MSKU.xls');

        $sheet = $PHPExcel->getSheet(0); //获取指定的sheet表
        $rows  = $sheet->getHighestRow();//行数
        $cols  = $sheet->getHighestColumn();//列数

        $data = array();
        for ($i = 2; $i <= $rows; $i++) { //行数是以第1行开始
            $count = 0;
            for ($j = 'A'; $j <= $cols; $j++) { //列数是以A列开始
                $value = $sheet->getCell($j . $i)->getValue();
                if ($value) {
                    $data[$i - 1][$count] = (string)$sheet->getCell($j . $i)->getValue();
                    $count                += 1;
                }
            }
        }


        $mSkuList = array_unique(array_column($data, '0'));

        $customSkuList = DB::table('self_custom_sku')->pluck('size', 'id');

        $oldSku = DB::table('self_sku')->pluck('custom_sku_id', 'old_sku');
        $newSku = DB::table('self_sku')->pluck('custom_sku_id', 'sku');


        $export = [];


        foreach ($mSkuList as $mSku) {
            $customSkuId = $oldSku[$mSku] ?? 0;
            $customSkuId = $customSkuId ? $customSkuId : ($newSku[$mSku] ?? 0);

            if (!$customSkuId) {
                continue;
            }
            $size = $customSkuList[$customSkuId] ?? '';
            if (!$size) {
                continue;
            }

            $export[] = [
                'msku' => $mSku,
                'size' => $size
            ];
        }

        $expCellName = [
            ['msku', 'msku'],
            ['size', 'size'],

        ];

        $this->exportExcel("exportMskuSize.xlsx", $expCellName, $export);

        exit('程序结束');
    }


    public function exportSkuPrice()
    {
        $skuList = DB::table('self_custom_sku')
            ->select('id', 'custom_sku', 'name', 'img', 'buy_price', 'spu_id', 'base_spu_id')
//            ->where('lingxing_id', 0)
//            ->where('custom_sku', 'ZITY0455-B01-2XL')
            ->get();


        $priceKeyBySpuId = $this->getContractPrice();//获取合同价格

        $spuInfoKeyByBaseSpuId = Db::table('self_spu_info')->pluck('unit_price', 'base_spu_id');

        $count = $skuList->count();
        $n     = 1;

        $exportArr = [];

        foreach ($skuList as $sku) {
            echo $count . '___' . $n . PHP_EOL;
            $n++;

            $price = $priceKeyBySpuId[$sku->spu_id] ?? 0;

            if (!$price) {
                $price = $spuInfoKeyByBaseSpuId[$sku->base_spu_id] ?? 0;
            }

            $exportArr[] = [
                'sku'   => $sku->custom_sku,
                'price' => $price * 1.1,
            ];
        }


        $expCellName = [
            ['sku', '品名'],
            ['price', '成本'],
        ];

        $exportArr = array_chunk($exportArr, 5000);

        foreach ($exportArr as $key => $export) {
            $this->exportExcel("产品成本$key.xlsx", $expCellName, $export);
        }

        exit('程序结束');
    }

    public function getContractPrice($spuId = 0)
    {
        //取合同里出现价格次数最多的价格 如果出现的次数一样，取价格最大的
        $priceList = DB::table('cloudhouse_contract')
            ->select('spu_id', 'one_price', DB::raw('count(id) num'))
            ->where('spu_id', '>', 0)
            ->where('one_price', '>', 0)
            ->groupBy('spu_id', 'one_price')
            ->get();

        $priceListKeyBySpuId = [];
        foreach ($priceList as $price) {
            $priceListKeyBySpuId[$price->spu_id][] = $price;
        }


        $priceKeyBySpuId = [];
        foreach ($priceListKeyBySpuId as $spuId => $priceList) {
            $num   = 0;
            $price = 0;
            foreach ($priceList as $value) {
                if ($value->num < $num || $value->one_price < $price) continue;

                $num   = $value->num;
                $price = $value->one_price;
            }
            $priceKeyBySpuId[$spuId] = $price;
        }

        return $priceKeyBySpuId;
    }

    //采集亚马逊sku库龄数据
    public function storageAge(){
        $lxApi = new LingXingApiController();

        //获取领星所有店铺
        $sellerList = $lxApi->getSellerList();
        if ($sellerList['code'] != 0) {
            exit(json_encode($sellerList, JSON_UNESCAPED_UNICODE));
        }

        $product_detail = DB::table('product_detail')->where('now_num','>',0)->select('sku_id','in_stock_num','un_stock_num','in_bound_num','transfer_num')->get()->toArray();
        $skuIds = [];
        $invArr = [];
        foreach ($product_detail as $p){
            $skuIds[] = $p->sku_id;
            $invArr[$p->sku_id] = $p;
        }
        //sp广告活动报表
        $n = 1;
        foreach ($sellerList['data'] as $seller) {

            $sid   = $seller['sid'];

            echo $sid . '___' . $n . PHP_EOL;
            $n++;


            $p         = 1;    //页码
            $length    = 100;  //页数
            $totalPage = 0;    //总页数

            do {
                $params = [
                    'sid'    => $sid,
                    'offset' => ($p - 1) * $length,
                    'length' => $length,
                ];

                $res = $lxApi->GetStorageAge($params);


                if ($res['code'] != 0) {
                    self::writeLog('GetStorageAge', $params, $res);
                    continue;
                }

                $date = date("Y-m-d");
                foreach ($res['data']['list'] as $v) {
                    $self_sku = DB::table('self_sku')->where('sku',$v['sku'])->orWhere('old_sku',$v['sku'])->select('id','spu_id','spu','fasin','shop_id')->first();
                    if(empty($self_sku)){
                        self::writeLog('GetStorageAge', $params, 'xmly未查询到此sku'.$v['sku']);
                        continue;
                    }
                    if(!in_array($self_sku->id,$skuIds)){
                        continue;
                    }
                    $aged = DB::table('amazon_inventory_aged')->where('sku_id',$self_sku->id)->where('shop_id',$self_sku->shop_id)->where('snapshot_date',$date)
                        ->select('id')->first();
                    if($aged){
                        $update = [
                            'day_0_90'   => $v['inv_age_0_to_90_days'],
                            'day_91_180'    => $v['inv_age_91_to_180_days'],
                            'day_181_270'   => $v['inv_age_181_to_270_days'],
                            'day_271_365'  => $v['inv_age_271_to_365_days'],
                            'day_365'     => $v['inv_age_365_plus_days'],
                            'your_price'   => $v['your_price'],
                            'sales_price'  => $v['sales_price'],
                            'lowest_price_new_plus_shipping'  =>  $v['lowest_price_new_plus_shipping'],
                            'lowest_price_used' =>  $v['lowest_price_used'],
                            'item_volume' =>  $v['item_volume'],
                            'sales_rank' =>  $v['sales_rank'],
                            'estimated_storage_cost_next_month' =>  $v['estimated_storage_cost_next_month'],
                            'no_sale_last_6_months' =>  $v['no_sale_last_6_months']
                        ];
                        if(isset($invArr[$self_sku->id])){
                            $update['in_stock_num'] = $invArr[$self_sku->id]->in_stock_num;
                            $update['un_stock_num'] = $invArr[$self_sku->id]->un_stock_num;
                            $update['in_bound_num'] = $invArr[$self_sku->id]->in_bound_num;
                            $update['transfer_num'] = $invArr[$self_sku->id]->transfer_num;
                        }
                         DB::table('amazon_inventory_aged')->where('id',$aged->id)->update($update);
                    }else{
                        $insert = [
                            'shop_id'        => $self_sku->shop_id,
                            'snapshot_date' => $date,
                            'spu'    => $self_sku->spu,
                            'spu_id'         =>  $self_sku->spu_id,
                            'father_asin'   => $self_sku->fasin,
                            'sku'    => $v['sku'],
                            'sku_id'     => $self_sku->id,
                            'fnsku'    => $v['fnsku'],
                            'asin'    => $v['asin'],
                            'day_0_90'   => $v['inv_age_0_to_90_days'],
                            'day_91_180'    => $v['inv_age_91_to_180_days'],
                            'day_181_270'   => $v['inv_age_181_to_270_days'],
                            'day_271_365'  => $v['inv_age_271_to_365_days'],
                            'day_365'     => $v['inv_age_365_plus_days'],
                            'your_price'   => $v['your_price'],
                            'sales_price'  => $v['sales_price'],
                            'lowest_price_new_plus_shipping'  =>  $v['lowest_price_new_plus_shipping'],
                            'lowest_price_used' =>  $v['lowest_price_used'],
                            'item_volume' =>  $v['item_volume'],
                            'sales_rank' =>  $v['sales_rank'],
                            'estimated_storage_cost_next_month' =>  $v['estimated_storage_cost_next_month'],
                            'no_sale_last_6_months' =>  $v['no_sale_last_6_months']
                        ];
                        if(isset($invArr[$self_sku->id])){
                            $insert['in_stock_num'] = $invArr[$self_sku->id]->in_stock_num;
                            $insert['un_stock_num'] = $invArr[$self_sku->id]->un_stock_num;
                            $insert['in_bound_num'] = $invArr[$self_sku->id]->in_bound_num;
                            $insert['transfer_num'] = $invArr[$self_sku->id]->transfer_num;
                        }

                        $insertdata = DB::table('amazon_inventory_aged')->insert($insert);
                        if(!$insertdata){
                            self::writeLog('GetStorageAge', $params, ['message'=>'新增'.$v['sku'].'的库龄数据失败','res'=>$res]);
                        }
                    }

                }
                $totalPage = ceil($res['total'] / $length);
                $p++;
            } while ($p <= $totalPage);

        }

        exit('程序结束');

    }

    //sp广告商品报表
    public function spSkuReport(){
        $lxApi = new LingXingApiController();

        //获取领星所有店铺
        $sellerList = $lxApi->getSellerList();
        if ($sellerList['code'] != 0) {
            exit(json_encode($sellerList, JSON_UNESCAPED_UNICODE));
        }

        //sp广告活动报表
        $n = 1;

        $date = date("Y-m-d",strtotime("-1 days"));

        foreach ($sellerList['data'] as $seller) {

            $sid   = $seller['sid'];

            echo $sid . '___' . $n . PHP_EOL;
            $n++;


            $p         = 1;    //页码
            $length    = 100;  //页数
            $totalPage = 0;    //总页数

            do {
                $params = [
                    'sid'    => $sid,
                    'report_date'=>$date,
                    'show_detail' => 0,
                    'offset' => ($p - 1) * $length,
                    'length' => $length,
                ];

                $res = $lxApi->spProductAdReports($params);


                if ($res['code'] != 0) {
                    self::writeLog('spProductAdReports', $params, $res);
                    continue;
                }


                foreach ($res['data'] as $v) {
                    $self_sku = DB::table('self_sku')->where('sku',$v['sku'])->orWhere('old_sku',$v['sku'])->select('id','spu_id','shop_id')->first();
                    if(empty($self_sku)){
                        self::writeLog('spProductAdReports', $params, 'xmly未查询到此sku'.$v['sku']);
                        continue;
                    }

                    $insert = [
                        'shop_id'        => $self_sku->shop_id,
                        'report_date' => $v['report_date'],
                        'spu_id'         =>  $self_sku->spu_id,
                        'sku'    => $v['sku'],
                        'sku_id'     => $self_sku->id,
                        'asin'    => $v['asin'],
                        'ad_group_id'   => $v['ad_group_id'],
                        'campaign_id'    => $v['campaign_id'],
                        'impressions'   => $v['impressions'],
                        'clicks'  => $v['clicks'],
                        'cost'     => $v['cost'],
//                        'tactic'   => $v['tactic'],
//                        'view_impressions'  => $v['view_impressions'],
                        'same_orders'  =>  $v['same_orders'],
                        'orders' =>  $v['orders'],
                        'sales' =>  $v['sales'],
                        'same_sales' =>  $v['same_sales'],
                        'units' =>  $v['units'],
                        'type' =>  1
                    ];

                    $insertdata = DB::table('lingxing_ad_sku_report')->insert($insert);
                    if(!$insertdata){
                        self::writeLog('spProductAdReports', $params, ['message'=>'新增'.$v['sku'].'的广告数据失败','res'=>$res]);
                    }
                }
                $totalPage = ceil($res['total'] / $length);
                $p++;
            } while ($p <= $totalPage);

        }

        exit('程序结束');

    }

    //sp广告商品报表
    public function sdSkuReport(){
        $lxApi = new LingXingApiController();

        //获取领星所有店铺
        $sellerList = $lxApi->getSellerList();
        if ($sellerList['code'] != 0) {
            exit(json_encode($sellerList, JSON_UNESCAPED_UNICODE));
        }

        //sp广告活动报表
        $n = 1;

        $date = date("Y-m-d",strtotime("-1 days"));

        foreach ($sellerList['data'] as $seller) {

            $sid   = $seller['sid'];

            echo $sid . '___' . $n . PHP_EOL;
            $n++;


            $p         = 1;    //页码
            $length    = 100;  //页数
            $totalPage = 0;    //总页数

            do {
                $params = [
                    'sid'    => $sid,
                    'report_date'=>$date,
                    'show_detail' => 0,
                    'offset' => ($p - 1) * $length,
                    'length' => $length,
                ];

                $res = $lxApi->sdProductAdReports($params);


                if ($res['code'] != 0) {
                    self::writeLog('sdProductAdReports', $params, $res);
                    continue;
                }


                foreach ($res['data'] as $v) {
                    $self_sku = DB::table('self_sku')->where('sku',$v['sku'])->orWhere('old_sku',$v['sku'])->select('id','spu_id','shop_id')->first();
                    if(empty($self_sku)){
                        self::writeLog('sdProductAdReports', $params, 'xmly未查询到此sku'.$v['sku']);
                        continue;
                    }

                    $insert = [
                        'shop_id'        => $self_sku->shop_id,
                        'report_date' => $v['report_date'],
                        'spu_id'         =>  $self_sku->spu_id,
                        'sku'    => $v['sku'],
                        'sku_id'     => $self_sku->id,
                        'asin'    => $v['asin'],
                        'ad_group_id'   => $v['ad_group_id'],
                        'campaign_id'    => $v['campaign_id'],
                        'impressions'   => $v['impressions'],
                        'clicks'  => $v['clicks'],
                        'cost'     => $v['cost'],
//                        'tactic'   => $v['tactic'],
//                        'view_impressions'  => $v['view_impressions'],
                        'same_orders'  =>  $v['same_orders'],
                        'orders' =>  $v['orders'],
                        'sales' =>  $v['sales'],
                        'same_sales' =>  $v['same_sales'],
                        'units' =>  $v['units'],
                        'type' =>  2
                    ];

                    $insertdata = DB::table('lingxing_ad_sku_report')->insert($insert);
                    if(!$insertdata){
                        self::writeLog('sdProductAdReports', $params, ['message'=>'新增'.$v['sku'].'的广告数据失败','res'=>$res]);
                    }
                }
                $totalPage = ceil($res['total'] / $length);
                $p++;
            } while ($p <= $totalPage);

        }

        exit('程序结束');

    }


    public function exportExcel($fileName, $expCellName, $expTableData)
    {

        ini_set("memory_limit", "256M");

        $cacheMethod   = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
        $cacheSettings = array();
        \PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
//        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
//        $fileName = date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum  = count($expCellName);
        $dataNum  = count($expTableData);
        $PHPExcel = new \PHPExcel();
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');

        //$PHPExcel->getActiveSheet()->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
        // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));
        for ($i = 0; $i < $cellNum; $i++) {
            $PHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '1', $expCellName[$i][1]);
        }
        // Miscellaneous glyphs, UTF-8
        for ($i = 0; $i < $dataNum; $i++) {
            for ($j = 0; $j < $cellNum; $j++) {
                $PHPExcel->getActiveSheet()->setCellValueExplicit($cellName[$j] . ($i + 2), $expTableData[$i][$expCellName[$j][0]]);
            }
        }
        $objWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel5');
        $objWriter->save($fileName);
    }

    private static function writeLog($name, $params = [], $res = '')
    {
        DB::table('console_err')->insert([
            'name'        => $name,
            'params'      => $params ? json_encode($params, JSON_UNESCAPED_UNICODE) : '',
            'res'         => is_array($res) ? json_encode($res, JSON_UNESCAPED_UNICODE) : $res,
            'create_time' => date('Y-m-d H:i:s'),
        ]);
    }
}
